from sqlalchemy import Column, ForeignKey, DateTime
from sqlalchemy.orm import relationship
from sqlalchemy.dialects.oracle import VARCHAR2, NUMBER
from app.db.base import Base

# Sponsor Table
class Sponsor(Base):
    __tablename__ = 'SPONSOR'
    __table_args__ = {'schema': 'IARO_COOP_ADMIN'}

    sponsor_id = Column(NUMBER, primary_key=True)
    sponsor_code = Column(VARCHAR2(20))
    sponsor_name = Column(VARCHAR2(250))

    contacts = relationship("SponsorContact", back_populates="sponsor")
    sponsorships = relationship("Sponsorship", back_populates="sponsor")
    groups = relationship("SponsorGroup", back_populates="sponsor")

# SponsorContact Table
class SponsorContact(Base):
    __tablename__ = 'SPONSOR_CONTACT'
    __table_args__ = {'schema': 'IARO_COOP_ADMIN'}

    sponsor_contact_id = Column(NUMBER, primary_key=True)
    sponsor_id = Column(NUMBER, ForeignKey('IARO_COOP_ADMIN.SPONSOR.sponsor_id'))
    title = Column(VARCHAR2(100))
    first_name = Column(VARCHAR2(250))
    last_name = Column(VARCHAR2(250))
    position_title = Column(VARCHAR2(250))
    phone = Column(VARCHAR2(100))
    ec = Column(VARCHAR2(20))  # Extension
    gm = Column(VARCHAR2(20))  # General mobile
    kc = Column(VARCHAR2(20))  # Another contact field
    ss = Column(VARCHAR2(20))
    lk = Column(VARCHAR2(20))
    inv = Column(VARCHAR2(20))
    aa = Column(VARCHAR2(20))
    bb = Column(VARCHAR2(20))
    mobile = Column(VARCHAR2(30))
    fax = Column(VARCHAR2(20))
    email = Column(VARCHAR2(250))
    website = Column(VARCHAR2(250))
    last_updated = Column(DateTime)
    archive = Column(VARCHAR2(4000))
    hidden = Column(VARCHAR2(4000))

    sponsor = relationship("Sponsor", back_populates="contacts")

# SponsorGroup Table (association)
class SponsorGroup(Base):
    __tablename__ = 'SPONSOR_GROUP'
    __table_args__ = {'schema': 'IARO_COOP_ADMIN'}

    sponsor_group_id = Column(NUMBER, ForeignKey('IARO_COOP_ADMIN.SPONSOR_GROUP_LIST.sponsor_group_id'), primary_key=True)
    sponsor_id = Column(NUMBER, ForeignKey('IARO_COOP_ADMIN.SPONSOR.sponsor_id'), primary_key=True)

    group_list = relationship("SponsorGroupList", back_populates="sponsor_associations")
    sponsor = relationship("Sponsor", back_populates="groups")


# SponsorGroupList Table
class SponsorGroupList(Base):
    __tablename__ = 'SPONSOR_GROUP_LIST'
    __table_args__ = {'schema': 'IARO_COOP_ADMIN'}

    sponsor_group_id = Column(NUMBER, primary_key=True)
    sponsor_group_code = Column(VARCHAR2(100))
    sponsor_group_name = Column(VARCHAR2(1000))

    sponsor_associations = relationship("SponsorGroup", back_populates="group_list")


# Sponsorship Table
class Sponsorship(Base):
    __tablename__ = 'SPONSORSHIP'
    __table_args__ = {'schema': 'IARO_COOP_ADMIN'}

    sponsorship_id = Column(NUMBER, primary_key=True)
    sponsor_id = Column(NUMBER, ForeignKey('IARO_COOP_ADMIN.SPONSOR.sponsor_id'))
    program_id = Column(NUMBER, ForeignKey('IARO_COOP_ADMIN.PROGRAM.program_id'))
    year = Column(NUMBER)

    sponsor = relationship("Sponsor", back_populates="sponsorships")
    program = relationship("Program", back_populates="sponsorships")
    places = relationship("SponsorshipPlaces", back_populates="sponsorship")
    
# SponsorshipPlaces Table
class SponsorshipPlaces(Base):
    __tablename__ = 'SPONSORSHIP_PLACES'
    __table_args__ = {'schema': 'IARO_COOP_ADMIN'}

    sponsorship_place_id = Column(NUMBER, primary_key=True)
    sponsorship_id = Column(NUMBER, ForeignKey('IARO_COOP_ADMIN.SPONSORSHIP.sponsorship_id'))

    sponsorship = relationship("Sponsorship", back_populates="places")

